﻿using System;
using System.Collections.Generic;
using System.Data;

namespace Finance.Data.Entities
{
	public interface IContractors : IDataBaseObjectCollection<IContractor>
	{
		IEnumerable<IContractor> GetAll();
		IContractor Create(string contractorName);
	}

	internal class Contractors : DataBaseObjectCollection<IContractor>, IContractors
	{
		private const string UpdateQuery = "update contractor set name = @p0, description = @p1 where contractorid = @p2";
		private const string CreateQuery = "insert into contractor (contractorid, name, createdOn) values (@p0, @p1, getdate())";
		private const string RetrieveContractor = "select contractorId, Name, Description from contractor order by Name";
		private const string RetrieveContractorByName = "select contractorId, Name, Description from contractor where Name=@p0";
		private const string RetrieveContractorById = "select contractorId, Name, Description from contractor where contractorId=@p0";

		public Contractors (DataBase dataBase) : base(dataBase, "contractor")
		{
			
		}

		public IEnumerable<IContractor> GetAll()
		{
			using (var dataReader = DataBase.Sql.ExecuteReader(RetrieveContractor))
			{
				while(dataReader.Read())
				{
					yield return ParseItem(dataReader);
				}
			}
		}

		public override IContractor GetItem(Guid guid)
		{
			if (guid == Guid.Empty)
				return null;

			using (var dataReader = DataBase.Sql.ExecuteReader(RetrieveContractorById, guid))
			{
				if (dataReader.Read())
					return ParseItem(dataReader);
			}
			return null;
		}

		protected override IContractor ParseItem(IDataRecord dataReader)
		{
			var contractor = new Contractor(this)
				{
					Id = (Guid) dataReader["contractorId"],
					Name = (string) dataReader["Name"],
					Description = GetString(dataReader, "Description")
				};
			return contractor;
		}

		internal IContractor FindByName(string name)
		{
			using(var dataReader = DataBase.Sql.ExecuteReader(RetrieveContractorByName, name))
			{
				if(dataReader.Read())
					return ParseItem(dataReader);	
			}
			return null;
		}

		internal IContractor GetOrCreate(string contractorName)
		{
			return FindByName(contractorName) ?? Create(contractorName);
		}

		public IContractor Create(string contractorName)
		{
			var contractor = new Contractor(this) { Name = contractorName };
            Insert(contractor);
			return contractor;
		}

		protected override void Update(IContractor contractor)
		{
			DataBase.Sql.ExecuteNonQuery(UpdateQuery, contractor.Name, contractor.Description, contractor.Id);
		}

		protected override void Insert(IContractor contractor)
		{
			if (contractor.Id == Guid.Empty)
				contractor.Id = Guid.NewGuid();

			using (var sql = ((FinanceDataBase)DataBase).OpenCommandProcessor())
			{
				sql.TransactionBegin();
				sql.ExecuteNonQuery(
					CreateQuery,
					contractor.Id,
					contractor.Name);
				sql.TransactionCommit();
			}
		}

		public override IContractor NewItem()
		{
			return new Contractor(this);
		}

		public override void Save(object item)
		{
			Save((Contractor)item);
		}
	}
}
